﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace FU.SE0208.G1.Controller
{
    // Interact with DB
    public static class DbHandler
    {
        // Connection string
        private static string sConn = "Data Source=.;Initial Catalog=LSM;Integrated Security=True";

        // Create new connection
        public static SqlConnection getConn()
        {
            SqlConnection conn = new SqlConnection(sConn);

            return conn;
        }

        // Create connection string
        public static void setStringConn(string host, string dbName, string userName, string password)
        {
            // Read information from file

            // Create connection string
            sConn = "Data Source=" + host + ";";
            sConn += "User ID=" + userName + ";";
            sConn += "Password=" + password + ";";
            sConn += "Initial Catalog=" + dbName;
        }

        // Run SQL command which make change in DB
        public static bool doQuery(String sSQL)
        {
            SqlConnection conn = getConn();

            try
            {
                conn.Open();

                SqlCommand command = conn.CreateCommand();
                command.CommandType = CommandType.Text;
                command.CommandText = sSQL;

                return command.ExecuteNonQuery() > 0;
            }
            catch (Exception ex)
            {
                // run SQL command fail
                return false;
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
        }

        // Run SQL command to get data
        public static bool getQuery(DataSet ds, string strSQL, string strTable)
        {
            SqlConnection conn = getConn();

            try
            {
                SqlDataAdapter da = new SqlDataAdapter(strSQL, conn);

                if (strTable != null) da.Fill(ds, strTable);
                else da.Fill(ds);

                return true;
            }
            catch (Exception ex)
            {
                // run SQL command fail
                return false;
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
            }
        }
    }
}
